<?php
require_once('./php_lib/conexion.php');
include('./pdf/class.ezpdf.php');

$pdf =& new Cezpdf('a4');
$pdf->selectFont('./pdf/fonts/Helvetica.afm');
$pdf->ezSetCmMargins(1,1,1.5,1.5); 
$pdf->ezText("<b>Informe de Ventas Calzado</b>\n",16);
$options = array(
                'shadeCol'=>array(0.9,0.9,0.9),
                'xOrientation'=>'center',
                'width'=>500
            );
$pdf->ezText(utf8_decode("Informe para el periodo entre ".$_POST['datepicker1']." al ".$_POST['datepicker2']."\n"),14);

$pdf->ezText(utf8_decode("Los 10 productos más vendidos\n"),12);
$query = "SELECT Producto.prod_id,mar_descripcion,mod_descripcion,sum(prodv_can) AS Cantidad, sum(prodv_valor) AS `Total Ganacia`
FROM `Producto`,`Prod_Ven`,(SELECT mod_descripcion,mar_descripcion,Modelo.mod_id
FROM `Modelo`,`Marca` Where Modelo.mar_id = Marca.mar_id) AS Marca,
(Select ven_id From Venta, Personal 
where Venta.Per_rut = Personal.per_rut
And suc_id".$_POST['suc']."
And Venta.ven_fec between '".$_POST['datepicker1']." 00:00:00' and '".$_POST['datepicker2']." 23:59:59') As Venta
Where Producto.prod_id = Prod_Ven.prod_id
And Prod_Ven.ven_id = Venta.ven_id
And Producto.mod_id = Marca.mod_id
group by Prod_Ven.prod_id
Order by Cantidad DESC
Limit 0,10";
$Recordset = mysql_query($query, $db_zapatos) or die(mysql_error());
$totalRows_Recordset = mysql_num_rows($Recordset);

	
	$ixx = 0;
	while($datatmp = mysql_fetch_assoc($Recordset)) {
    	$ixx = $ixx+1;
    	$data0[] = array_merge($datatmp, array('num'=>$ixx));
	}
	$titles= array(
                'prod_id'=>'<b>Producto Id</b>',
                'mar_descripcion'=>'<b>Marca</b>',
                'mod_descripcion'=>'<b>Modelo</b>',
                'Cantidad'=>'<b>Cantidad</b>',
                'Total Ganacia'=>'<b>Total Ganancia</b>',

            );
$pdf->ezTable($data0,$titles,'',$options );
$pdf->ezText("\n\n\n",10);

$pdf->ezText("Mejor(es) Producto(s)\n",12);
$query_Recordset1 = "Select prod_id,mar_descripcion,mod_descripcion,col_descripcion,talla_num From Color,Talla, Producto,
(Select mar_descripcion,mod_descripcion,mod_id From Modelo, Marca Where Marca.mar_id = Modelo.mar_id) As Marc,
(Select prod_id As ID From 
	(Select prod_id,sum(prodv_can) As Suma From Prod_Ven,
		(Select ven_id From Venta, Personal where Venta.Per_rut = Personal.per_rut And suc_id".$_POST['suc']."
		And Venta.ven_fec between '".$_POST['datepicker1']." 00:00:00' and '".$_POST['datepicker2']." 23:59:59') As Venta
		Where Prod_Ven.ven_id =Venta.ven_id group by prod_id) As Cantidad,
		(Select Max(Suma) As Mejor From
			(Select prod_id,sum(prodv_can) As Suma From Prod_Ven,
				(Select ven_id From Venta, Personal where Venta.Per_rut = Personal.per_rut And suc_id".$_POST['suc']."
				And Venta.ven_fec between '".$_POST['datepicker1']." 00:00:00' and '".$_POST['datepicker2']." 23:59:59') As Venta
			Where Prod_Ven.ven_id =Venta.ven_id group by prod_id)As Cantidad
		)As M
	Where Mejor=Suma) As D
Where prod_id = ID 
And Producto.mod_id = Marc.mod_id
And Producto.talla_id = Talla.talla_id
And Producto.col_id = Color.col_id";
$Recordset1 = mysql_query($query_Recordset1, $db_zapatos) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


$ixx = 0;
while($datatmp = mysql_fetch_assoc($Recordset1)) {
$ixx = $ixx+1;
$data1[] = array_merge($datatmp, array('num'=>$ixx));
}
$titles = array(
'prod_id'=>'<b>Id del Producto</b>',
'mar_descripcion'=>'<b>Marca</b>',
'mod_descripcion'=>'<b>Modelo</b>',
'col_descripcion'=>'<b>Color</b>',
'talla_num'=>'<b>Talla</b>',
);
$pdf->ezTable($data1,$titles,'',$options );
$pdf->ezText("\n\n\n",10);

$pdf->ezText("Peor(es) Producto(s)\n",12);
$query_Recordset1 = "Select prod_id,mar_descripcion,mod_descripcion,col_descripcion,talla_num From Color,Talla, Producto,
(Select mar_descripcion,mod_descripcion,mod_id From Modelo, Marca Where Marca.mar_id = Modelo.mar_id) As Marc,
(Select prod_id As ID From 
	(Select prod_id,sum(prodv_can) As Suma From Prod_Ven,
		(Select ven_id From Venta, Personal where Venta.Per_rut = Personal.per_rut And suc_id".$_POST['suc']."
		And Venta.ven_fec between '".$_POST['datepicker1']." 00:00:00' and '".$_POST['datepicker2']." 23:59:59') As Venta
		Where Prod_Ven.ven_id =Venta.ven_id group by prod_id) As Cantidad,
		(Select Min(Suma) As Mejor From
			(Select prod_id,sum(prodv_can) As Suma From Prod_Ven,
				(Select ven_id From Venta, Personal where Venta.Per_rut = Personal.per_rut And suc_id".$_POST['suc']."
				And Venta.ven_fec between '".$_POST['datepicker1']." 00:00:00' and '".$_POST['datepicker2']." 23:59:59') As Venta
			Where Prod_Ven.ven_id =Venta.ven_id group by prod_id)As Cantidad
		)As M
	Where Mejor=Suma) As D
Where prod_id = ID 
And Producto.mod_id = Marc.mod_id
And Producto.talla_id = Talla.talla_id
And Producto.col_id = Color.col_id";
$Recordset1 = mysql_query($query_Recordset1, $db_zapatos) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


$ixx = 0;
while($datatmp = mysql_fetch_assoc($Recordset1)) {
$ixx = $ixx+1;
$data11[] = array_merge($datatmp, array('num'=>$ixx));
}
$titles = array(
'prod_id'=>'<b>Id del Producto</b>',
'mar_descripcion'=>'<b>Marca</b>',
'mod_descripcion'=>'<b>Modelo</b>',
'col_descripcion'=>'<b>Color</b>',
'talla_num'=>'<b>Talla</b>',
);
$pdf->ezTable($data11,$titles,'',$options );
$pdf->ezText("\n\n\n",10);

$pdf->ezText(utf8_decode("Marca más Vendida\n"),12);
$query_Recordset1 = "Select mar_descripcion,(sum(prodv_can)*100)/T.total As Porcentaje From Prod_Ven, Producto,(Select ven_id From Venta, Personal Where Venta.per_rut = Personal.per_rut And suc_id".$_POST['suc']."
And Venta.ven_fec between '".$_POST['datepicker1']." 00:00:00' and '".$_POST['datepicker2']." 23:59:59') As Venta,
(Select mod_id, mar_descripcion From Marca,Modelo Where Marca.mar_id = Modelo.mar_id) As Marc,(Select sum(prodv_can) AS total From Prod_Ven,(Select ven_id From Venta, Personal where Venta.per_rut = Personal.per_rut And suc_id".$_POST['suc']." 
And Venta.ven_fec between '".$_POST['datepicker1']." 00:00:00' and '".$_POST['datepicker2']." 23:59:59') As Venta
		Where Prod_Ven.ven_id =Venta.ven_id) AS T 
Where Prod_Ven.ven_id =Venta.ven_id
And Producto.prod_id = Prod_Ven.prod_id
And Marc.mod_id = Producto.mod_id
Group by mar_descripcion
Order by Porcentaje Desc";
$Recordset1 = mysql_query($query_Recordset1, $db_zapatos) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


$ixx = 0;
while($datatmp = mysql_fetch_assoc($Recordset1)) {
$ixx = $ixx+1;
$data3[] = array_merge($datatmp, array('num'=>$ixx));
}
$titles = array(
'mar_descripcion'=>'<b>Marca</b>',
'Porcentaje'=>'<b>Porcentaje %</b>',
);
$pdf->ezTable($data3,$titles,'',$options );
$pdf->ezText("\n\n\n",10);

if($_POST['suc']=='!=0'){
$pdf->ezText("Total ventas\n",12);
}
else{
$pdf->ezText("Total ventas por sucursal\n",12);
}
$query_Recordset1 = "Select suc_des, count(prod_id) As Total,sum(prodv_valor) As Suma From Prod_Ven,(Select ven_id,Suc.suc_des
From Venta,(Select suc_des , per_rut From Personal,Sucursal Where Sucursal.suc_id = Personal.suc_id And Sucursal.suc_id!=0) As Suc
Where Venta.per_rut = Suc.per_rut
And Venta.ven_fec between '".$_POST['datepicker1']." 00:00:00' and '".$_POST['datepicker2']." 23:59:59') As Venta
Where Prod_Ven.ven_id = Venta.ven_id
Group by suc_des
Order by Total Desc";
$Recordset1 = mysql_query($query_Recordset1, $db_zapatos) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


$ixx = 0;
while($datatmp = mysql_fetch_assoc($Recordset1)) {
$ixx = $ixx+1;
$data2[] = array_merge($datatmp, array('num'=>$ixx));
}
$titles = array(
'suc_des'=>'<b>Sucursal</b>',
'Total'=>'<b>Ventas</b>',
'Suma'=>'<b>Ganancias</b>',
);
$pdf->ezTable($data2,$titles,'',$options );
$pdf->ezText("\n\n\n",10);

if($_POST['suc']=='!=0'){
$pdf->ezText("Stock total\n",12);
}
else{
$pdf->ezText("Stock por sucursal\n",12);
}
$query_Recordset1 = "Select Producto.prod_id As ID,mar_descripcion,mod_descripcion,talla_num,col_descripcion,Total From Color,Talla, Producto,(Select mar_descripcion,mod_descripcion,mod_id From Modelo, Marca Where Marca.mar_id = Modelo.mar_id) As Marc,(Select prod_id, sum(pros_stock) AS Total
From Pro_Sec
Where sec_id in (Select sec_id From Sector, Bodega 
Where Bodega.bod_id = Sector.bod_id
And suc_id ".$_POST['suc'].")
Group by prod_id) As Stock Where 
Producto.prod_id = Stock.prod_id And
Producto.mod_id = Marc.mod_id And Producto.talla_id = Talla.talla_id And
Producto.col_id = Color.col_id
Order by Total Desc";
$Recordset1 = mysql_query($query_Recordset1, $db_zapatos) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


$ixx = 0;
while($datatmp = mysql_fetch_assoc($Recordset1)) {
$ixx = $ixx+1;
$data7[] = array_merge($datatmp, array('num'=>$ixx));
}
$titles = array(
'ID'=>'<b>Id del Producto</b>',
'mar_descripcion'=>'<b>Marca</b>',
'mod_descripcion'=>'<b>Modelo</b>',
'talla_num'=>'<b>Talla</b>',
'col_descripcion'=>'<b>Color</b>',
'Total'=>'<b>Stock</b>',
);
$pdf->ezTable($data7,$titles,'',$options );
$pdf->ezText("\n\n\n",10);
$pdf->ezText("<b>Fecha:</b> ".date("d/m/Y"),10);
$pdf->ezText("<b>Hora:</b> ".date("H:i:s")."\n\n",10);
$pdf->ezStream();

    ?>